
	**************** 
	** Clean life expectancy data (2019)
	****************
	
	** Income groups
	
		import excel using "$dir/rawdata/Income Groups/CLASS.xlsx", firstrow clear
			drop Lendingcategory OtherEMUorHIPC
			
			ren Economy country
			ren Code country_code
			ren Region region
			ren Incomegroup incomegroup
			
			tempfile incomegroups
			save `incomegroups', replace

		
	** Life expectancy at age sixty by country
		
		insheet using "$dir/rawdata/Life Expectancy/WHO_LifeExpectancy.csv", clear
			
			keep if period==2019
			
			keep indicator spatialdimvaluecode location period dim1 value
			
			ren location country
			ren spatialdimvaluecode country_code
			ren dim1 group
			ren period year
			
			compress
			
			gen temp=0 if indicator=="Life expectancy at birth (years)"
			replace temp=60 if indicator=="Life expectancy at age 60 (years)"
			
			drop indicator
			
			reshape wide value, i(country_code country year group) j(temp)
			
			replace group="total" if group=="Both sexes"
			replace group="male" if group=="Male"
			replace group="female" if group=="Female"
			
			reshape wide value0 value60,  i(country_code country year) j(group) string
			
			ren value60* lifeexpsixty*
			ren value0* lifeexpbirth*
			
			drop year 
			
			tempfile lifeexpectancies
			save `lifeexpectancies', replace			
		
	** Total population
	
		insheet using "$dir/rawdata/Population/API_SP.POP.TOTL_DS2_en_csv_v2_5358404.csv", names clear
		
			drop if _n==1
			ren datasource country
			ren worlddevelopmentindicators country_code
			drop v3 v4
			foreach var of varlist v5-v67 {
				di "`var'"
				sum `var' if _n==1
				ren `var' population`r(mean)'
			}
			drop population
			drop if _n==1
			
			keep country country_code population2019
			
			tempfile pop
			save `pop', replace
	
	** Male population
	
		insheet using "$dir/rawdata/Population/API_SP.POP.TOTL.MA.IN_DS2_en_csv_v2_5463043.csv", names clear
			
			drop if _n==1
			ren datasource country
			ren worlddevelopmentindicators country_code
			drop v3 v4
			foreach var of varlist v5-v67 {
				di "`var'"
				sum `var' if _n==1
				ren `var' malepopulation`r(mean)'
			}
			drop if _n==1
			
			keep country country_code malepopulation2019
			
			isid country_code
			tempfile mpop
			save `mpop', replace
			
	** Female population
	
		insheet using "$dir/rawdata/Population/API_SP.POP.TOTL.FE.IN_DS2_en_csv_v2_5457183.csv", names clear
			
			drop if _n==1
			ren datasource country
			ren worlddevelopmentindicators country_code
			drop v3 v4
			foreach var of varlist v5-v67 {
				di "`var'"
				sum `var' if _n==1
				ren `var' femalepopulation`r(mean)'
			}
			drop if _n==1
			
			keep country country_code femalepopulation2019
			
			isid country_code
			
			tempfile fpop
			save `fpop', replace
			
		use `pop', clear
		
		merge 1:1 country_code using `mpop', nogen assert(matched)
		merge 1:1 country_code using `fpop', nogen assert(matched)
		
		save `pop', replace
		
	** Merge with life expectancies
		
		use `lifeexpectancies', clear
		merge 1:1 country_code using `incomegroups', keep(matched) nogen
		merge 1:1 country_code using `pop', assert(using matched) keep(matched) nogen
		
		order region incomegroup
		sort region incomegroup country
		compress
		
	** Compute SSA averages
	
		replace incomegroup="HICs" if incomegroup=="High income"
		replace incomegroup="LMICs_nonSSA" if incomegroup=="Low income" | incomegroup=="Lower middle income" | incomegroup=="Upper middle income"
		replace incomegroup="" if region=="Sub-Saharan Africa"
		
		bys region: egen lifeexpbirthtotal_region=wtmean(lifeexpbirthtotal), weight(population2019)
		bys region: egen lifeexpbirthfemale_region=wtmean(lifeexpbirthfemale), weight(malepopulation2019)
		bys region: egen lifeexpbirthmale_region=wtmean(lifeexpbirthmale), weight(femalepopulation2019)
		
		bys region: egen lifeexpsixtytotal_region=wtmean(lifeexpsixtytotal), weight(population2019)
		bys region: egen lifeexpsixtyfemale_region=wtmean(lifeexpsixtyfemale), weight(malepopulation2019)
		bys region: egen lifeexpsixtymale_region=wtmean(lifeexpsixtymale), weight(femalepopulation2019)
		
		bys incomegroup: egen lifeexpbirthtotal_incgroup=wtmean(lifeexpbirthtotal), weight(population2019)
		bys incomegroup: egen lifeexpbirthfemale_incgroup=wtmean(lifeexpbirthfemale), weight(malepopulation2019)
		bys incomegroup: egen lifeexpbirthmale_incgroup=wtmean(lifeexpbirthmale), weight(femalepopulation2019)
		
		bys incomegroup: egen lifeexpsixtytotal_incgroup=wtmean(lifeexpsixtytotal), weight(population2019)
		bys incomegroup: egen lifeexpsixtyfemale_incgroup=wtmean(lifeexpsixtyfemale), weight(malepopulation2019)
		bys incomegroup: egen lifeexpsixtymale_incgroup=wtmean(lifeexpsixtymale), weight(femalepopulation2019)
		
		preserve
			keep if region=="Sub-Saharan Africa"
			keep region life*region
			duplicates drop
			ren region country
			gen country_code="SSA"
			
			ren *_region *
			
			tempfile ssa
			save `ssa', replace
			
		restore
			
		preserve
			keep if incomegroup=="HICs"
			keep incomegroup life*incgroup
			duplicates drop
			ren incomegroup country
			gen country_code="HICs"
			
			ren *_incgroup *
			
			tempfile hics
			save `hics', replace
		restore
		
		preserve
			keep if incomegroup=="LMICs_nonSSA" & region!="Sub-Saharan Africa"
			keep incomegroup life*incgroup
			duplicates drop
			ren incomegroup country
			gen country_code="LMICs_nonSSA"
			
			ren *_incgroup *
			
			tempfile lmics
			save `lmics', replace
		restore
		
		append using `ssa'
		append using `hics'
		append using `lmics'
		
		drop *_region *_incgroup population malepop femalepop
		
		gen keep=1 if country=="Sub-Saharan Africa" | country=="HICs" | country=="LMICs_nonSSA"
		replace keep=1 if country=="Ethiopia"
		replace keep=1 if country=="Nigeria"
		replace keep=1 if country=="United Republic of Tanzania"
		replace keep=1 if country=="Uganda"
		replace keep=1 if country=="Malawi"
		replace keep=1 if country=="South Africa"
		replace keep=1 if country=="Ghana"
		replace keep=1 if country=="Niger"
		keep if keep==1 
		drop keep
		
		replace country="Tanzania" if country=="United Republic of Tanzania"
		
		drop region	incomegroup
		
		compress
		
		save "$dir/data/lifeexpectancy2019.dta", replace
		